import pymssql, datetime, requests, json, calendar
from Utils.gy import timeklpk


def Database():  # 客流数据库session
    server = 'moonwife.top:9800'
    user = 'sa'
    password = 'ipva@07'
    database = 'IPVA_S0400_B1'
    kldb = pymssql.connect(server, user, password, database)
    cursor = kldb.cursor()
    return cursor


def get_monthkldata():  # 提取项目数据本月客流数据
    server = 'moonwife.top:9800'
    user = 'sa'
    password = 'ipva@07'
    database = 'IPVA_S0400_B1'
    kldb = pymssql.connect(server, user, password, database)
    cursor = kldb.cursor()
    last_sql = '''
    select InSum
    from Summary_Day
    where
    SiteKey='P00021'
    and
    datediff(month ,CountDate,getdate())=0;
    '''
    cursor.execute(last_sql)
    last_row = cursor.fetchall()
    return last_row
    cursor.close()


def get_moonthpark():  # 获取当月车流数据
    now = datetime.date.today()
    this_month_start = datetime.datetime(now.year, now.month, 1).strftime('%Y-%m-%d')
    this_month_end = datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]).strftime(
        '%Y-%m-%d')
    parkurl = 'http://10.105.146.46/park/home/stat/flow.htm?1596302938589&startTime=' + str(
        this_month_start) + '%2000%3A00%3A00&endTime=' + str(
        this_month_end) + '%2023%3A59%3A59&averageFlag=0&statType=1&loginOperatorName=wangrui&pwd=df6ebbd720969fa1bd08a86b220ae989'
    session = requests.session()
    preq = session.get(parkurl)
    preq.content.decode()
    pkjson = json.loads(preq.text)
    pkall = pkjson['data']['list']
    carinall = []
    for i in pkall:
        carinall.append(i['totalFlowIn'])

    return carinall


def get_Dayklinsum():  # 获取当日客流排名前十通道客流
    today = datetime.date.today().strftime('%Y%m%d')
    server = 'moonwife.top:9800'
    user = 'sa'
    password = 'ipva@07'
    database = 'IPVA_S0400_B1'
    kldb = pymssql.connect(server, user, password, database)
    cursor = kldb.cursor()
    Insum = '''
    select top 10 InSum
    from Summary_Day
    where DateKey= ''' + str(today) + '''
    and
    SiteKey!='P00021'
    Order by InSum desc;
    '''
    cursor.execute(Insum)
    Insum_row = cursor.fetchall()
    return Insum_row
    cursor.close()


def get_Dayklsitekey():  # 获取当日客流排名前十通道客流ID
    today = datetime.date.today().strftime('%Y%m%d')
    server = 'moonwife.top:9800'
    user = 'sa'
    password = 'ipva@07'
    database = 'IPVA_S0400_B1'
    kldb = pymssql.connect(server, user, password, database)
    cursor = kldb.cursor()
    Sitekey = '''
    select top 10 Sitekey
    from Summary_Day
    where DateKey= ''' + str(today) + '''
    and
    SiteKey!='P00021'
    Order by InSum desc;
    '''
    cursor.execute(Sitekey)
    Sitekey_row = cursor.fetchall()  # 这里先获取到通道ID
    return Sitekey_row
    cursor.close()


def get_Dayklname():  # 获取前10客流名
    Sitekey_rows = get_Dayklsitekey()
    cu = Database()
    name1 = '\'' + Sitekey_rows[0][0] + '\''
    name2 = '\'' + Sitekey_rows[1][0] + '\''
    name3 = '\'' + Sitekey_rows[2][0] + '\''
    name4 = '\'' + Sitekey_rows[3][0] + '\''
    name5 = '\'' + Sitekey_rows[4][0] + '\''
    name6 = '\'' + Sitekey_rows[5][0] + '\''
    name7 = '\'' + Sitekey_rows[6][0] + '\''
    name8 = '\'' + Sitekey_rows[7][0] + '\''
    name9 = '\'' + Sitekey_rows[8][0] + '\''
    name10 = '\'' + Sitekey_rows[9][0] + '\''
    shopname1 = '''
    select SiteName
    from Traffic_Sites_History
    where SiteKey=''' + str(name1) + '''
    or SiteKey=''' + str(name2) + '''
    or SiteKey=''' + str(name3) + '''
    or SiteKey=''' + str(name4) + '''
    or SiteKey=''' + str(name5) + '''
    or SiteKey=''' + str(name6) + '''
    or SiteKey=''' + str(name7) + '''
    or SiteKey=''' + str(name8) + '''
    or SiteKey=''' + str(name9) + '''
    or SiteKey=''' + str(name10) + ''';'''
    cu.execute(shopname1)
    shopname_row = cu.fetchall()
    return shopname_row
    cursor.close()


def get_daythistimekl():  # 获取当日此时客流
    cu = Database()
    now = datetime.date.today()
    oneday = datetime.timedelta(days=1)
    nowtime = (datetime.datetime.now() + datetime.timedelta(minutes=-10)).strftime('%H:%M:')
    now1 = '\'' + str(now - oneday) + ' 00:00:00.000' + '\''
    now2 = '\'' + str(now - oneday) + ' ' + str(nowtime) + '00.000' + '\''
    sql = '''
    select InSum
    from IPVA_S0400_B1.dbo.Summary_Ten_Noues
    where
    SiteKey='P00021'
    and
    CountDate>=''' + now1 + '''
    and
    CountDate<''' + now2 + '''
    ;
    '''
    cu.execute(sql)
    daytimekl = cu.fetchall()
    a = []
    for i in daytimekl:
        nubs = i[0]
        a.append(nubs)
    nub = sum(a)
    cu.close()
    return nub


def get_klPercentage():  # 与前一日此时客流对比
    firstkl = int(get_daythistimekl())
    thisday = int(timeklpk.get_kldata()[0])
    Percentage = float((thisday - firstkl) / firstkl * 100)
    Percentage = float('%.2f' % Percentage)
    Percentage = str(Percentage)
    return Percentage


def get_VisitDepth():  # 项目本月游逛深度
    cu = Database()
    thismonth = datetime.datetime.now().strftime('%m')  # 定义月份变量
    thisyear = datetime.datetime.now().strftime('%Y')  # 定义年份变量
    nowmonth = '\'' + str(thismonth) + '\''
    nowyear = '\'' + str(thisyear) + '\''
    get_VisitDepth_sql = '''
    select VisitDepth
    from IPVA_S0400_B1.dbo.Summary_Month
    where
    Month=''' + nowmonth + '''
    and
    Year=''' + nowyear + '''
    and
    SiteKey='P00021';
    '''
    cu.execute(get_VisitDepth_sql)
    VisitDepth = cu.fetchall()
    return VisitDepth


def get_Monthall():  # 获取本月总客流
    cu = Database()
    thismonth = datetime.datetime.now().strftime('%m')  # 定义月份变量
    thisyear = datetime.datetime.now().strftime('%Y')  # 定义年份变量
    nowmonth = '\'' + str(thismonth) + '\''
    nowyear = '\'' + str(thisyear) + '\''
    Allkl = '''
    select Insum
    from IPVA_S0400_B1.dbo.Summary_Month
    where
    Month=''' + nowmonth + '''
    and
    Year=''' + nowyear + '''
    and
    SiteKey='P00021';
    '''
    cu.execute(Allkl)
    Monthall = cu.fetchall()
    Monthkl = []
    for i in Monthall:
        a = i[0]
        Monthkl.append(a)
    Monthklone = Monthkl[0]
    Monthklone = float(Monthklone / 10000)
    Monthklone = float('%.2f' % Monthklone)
    return Monthklone

# if __name__ == '__main__':
#     get_daythistimekl()
